package com.xzx.car_accessories.dao.impl;

import com.mysql.cj.x.protobuf.MysqlxCrud;
import com.xzx.car_accessories.dao.CarAccessoryDao;
import com.xzx.car_accessories.dao.utilDao;
import com.xzx.car_accessories.domain.CarAccessory;
import com.xzx.car_accessories.domain.Inventory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author: geeshaw
 * @date: 2021/7/11 13:46
 * @email: shaw@bupt.edu.cn
 * @desc:
 **/

@Repository
public class CarAccessoryDaoImpl  {

    @Autowired
    private final JdbcTemplate jdbcTemplate;

    public CarAccessoryDaoImpl(JdbcTemplate jdbcTemplate)
    {
        this.jdbcTemplate = jdbcTemplate;
    }

    public int add_accessory(CarAccessory caraccessory) {
        String sql = "INSERT INTO `test`.`car_accessory`" +
                "(`cid`,`cname`,`category`,`manufacturer`,`create_time`,`update_time`) VALUES" +
                "(?, ?, ?, ?, ?, ?)";
        String create_time = utilDao.get_current_date();
        return jdbcTemplate.update(sql, caraccessory.getCid(), caraccessory.getCname(), caraccessory.getCategory(),
                caraccessory.getManufacturer(), create_time, create_time);
    }

    public int del_any_accessory(Map<String, Object> map)
    {
        String sql = "DELETE FROM `test`.`car_accessory` WHERE 1 = 1";
        List<Object> list = new ArrayList<>();
        if (map!=null){
            if (map.get("cid") != null){
                list.add(Long.parseLong(String.valueOf(map.get("cid"))));
                sql += " and cid = ?";
            }

            if (map.get("cname") != null){
                list.add(map.get("cname"));
                sql += " and cname = ?";
            }

            if (map.get("category") != null){
                list.add(map.get("category"));
                sql += " and category = ?";
            }

            if (map.get("manufacturer") != null){
                list.add(map.get("manufacturer"));
                sql += " and manufacturer = ?";
            }

        }
        return jdbcTemplate.update(sql, list.toArray());
    }
    public int del_accessory(String id)
    {
        System.out.println(id);
        String sql = "DELETE FROM `test`.`car_accessory` WHERE id = ?";
        return jdbcTemplate.update(sql, id);
    }

    public int update_accessory(CarAccessory c)
    {
//        String update_time = utilDao.get_current_date();
        String sql = "UPDATE `test`.`car_accessory` SET cid=?, " +
                " cname=?, category=?, manufacturer=?, update_time=?" +
                " WHERE cid = ?";
        return jdbcTemplate.update(sql, c.getCid(), c.getCname(), c.getCategory(), c.getManufacturer()
        ,c.getUpdate_time(), c.getCid());
    }

    // 分页查询
    public List<CarAccessory> findpage_accessory(int pageNum,  int pageSize)
    {
        String sql = "SELECT * FROM `test`.`car_accessory` LIMIT " + (pageNum-1) * pageSize + " , " + pageSize;
        List<CarAccessory> carAccessoryList;
        carAccessoryList = jdbcTemplate.query(sql, (resultSet, i) -> {
            CarAccessory carAccessory = new CarAccessory();
            carAccessory.setId(resultSet.getString("id"));
            carAccessory.setCid(resultSet.getLong("cid"));
            carAccessory.setCname(resultSet.getString("cname"));
            carAccessory.setCategory(resultSet.getString("category"));
            carAccessory.setManufacturer(resultSet.getString("manufacturer"));
            carAccessory.setCreate_time(resultSet.getString("create_time"));
            carAccessory.setUpdate_time(resultSet.getString("update_time"));
            return carAccessory;});
        return carAccessoryList;
    }

    public List<CarAccessory> search_findpage_accessory(int pageNum, int pageSize, String searchWord)
    {
        String sql = "SELECT * FROM `test`.`car_accessory` ";

        if(searchWord != "")
        {
            sql += "WHERE ";

            sql += " cid like '%" + searchWord + "%'";

            sql += " or cname like '%" + searchWord + "%'";

            sql += " or category like '%" + searchWord + "%'";

            sql += " or manufacturer like '%" + searchWord + "%'";
        }

        sql += (" LIMIT "  + (pageNum-1) * pageSize + " , " + pageSize);

        System.out.println(sql);

        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(CarAccessory.class));
    }

    public List<CarAccessory> search_accessory(String searchWord)
    {
        String sql = "SELECT * FROM `test`.`car_accessory` ";

        if(searchWord != "")
        {
            sql += "WHERE ";

            sql += " cid like '%" + searchWord + "%'";

            sql += " or cname like '%" + searchWord + "%'";

            sql += " or category like '%" + searchWord + "%'";

            sql += " or manufacturer like '%" + searchWord + "%'";
        }
//        List<Object> list = new ArrayList<>();
//
//        list.add("%"+searchWord+"%");
//        sql += " and cid like ?";
//
//        list.add("%"+searchWord+"%");
//        sql += " and cname like ?";
//
//        list.add("%"+searchWord+"%");
//        sql += " and category like ?";
//
//        list.add("%"+searchWord+"%");
//        sql += " and manufacturer like ?";
        System.out.println(sql);
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(CarAccessory.class));
//        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(CarAccessory.class), list.toArray());
    }

    public List<CarAccessory> get_diff_accessory(Map<String, Object> map){
        String sql = "SELECT * FROM `test`.`car_accessory` WHERE 1 = 1";
        List<Object> list = new ArrayList<>();
        if (map!=null){
            if (map.get("cid") != null){
                list.add("%"+Long.parseLong(String.valueOf(map.get("cid")))+"%");
                sql += " and cid like ?";
            }

            if (map.get("cname") != null){
                list.add("%"+ map.get("cname") +"%");
                sql += " and cname like ?";
            }

            if (map.get("category") != null){
                list.add("%"+map.get("category")+"%");
                sql += " and category like ?";
            }

            if (map.get("manufacturer") != null){
                list.add("%"+map.get("manufacturer")+"%");
                sql += " and manufacturer like ?";
            }

        }
//        Object[] arr = list.toArray();
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(CarAccessory.class), list.toArray());
    }

    public List<CarAccessory> get_all_accessories() {
        String sql = "SELECT * FROM `test`.`car_accessory`";
        List<CarAccessory> carAccessoryList;
        carAccessoryList = jdbcTemplate.query(sql, (resultSet, i) -> {
            CarAccessory carAccessory = new CarAccessory();
            carAccessory.setId(resultSet.getString("id"));
            carAccessory.setCid(resultSet.getLong("cid"));
            carAccessory.setCname(resultSet.getString("cname"));
            carAccessory.setCategory(resultSet.getString("category"));
            carAccessory.setManufacturer(resultSet.getString("manufacturer"));
            carAccessory.setCreate_time(resultSet.getString("create_time"));
            carAccessory.setUpdate_time(resultSet.getString("update_time"));
            return carAccessory;});

        return carAccessoryList;

    }

    public List<Map<String, String>> groupCategory()
    {
        String sql = "SELECT category, count(1) AS num FROM test.car_accessory GROUP BY category;";
        List<Map<String, String>> categoryList = jdbcTemplate.query(sql, (resultSet, i) -> {
            Map<String, String> category = new HashMap<String, String>();
            category.put("category", resultSet.getString("category"));
            category.put("num", resultSet.getString("num"));
            return category;
        }
        );
        return categoryList;

    }

    public List<Map<String, String>> groupManufacturer()
    {
        String sql = "SELECT manufacturer, count(1) as num FROM test.car_accessory GROUP BY manufacturer;";
        List<Map<String, String>> list = jdbcTemplate.query(sql, (resultSet, i) -> {
                    Map<String, String> map = new HashMap<String, String>();
                    map.put("manufacturer", resultSet.getString("manufacturer"));
                    map.put("num", resultSet.getString("num"));
                    return map;
                }
        );
        return list;

    }


}
